﻿-- function to compare Xml based upon the function developed by Jacob Sebastian (Beyond Relational blog)
-- see the following two blog posts:
-- http://beyondrelational.com/blogs/jacob/archive/2008/09/14/xquery-lab-25-writing-a-tsql-function-to-compare-two-xml-values-part-1.aspx
-- http://beyondrelational.com/blogs/jacob/archive/2008/09/14/xquery-lab-36-writing-a-tsql-function-to-compare-two-xml-values-part-2.aspx
create function [dbo].[CompareXml]
(
	@xml1 xml 
	, @xml2 xml
)
returns int
as
begin
	declare @return int
	select @return = 0
	
	-- if both arguments are null we'll consider them equal
	if @xml1 is null and @xml2 is null
	begin
		return 0
	end
	
	-- if one of the arguments is NULL we can assume they are not equal.
	if @xml1 is null or @xml2 is null
	begin
		return 1
	end
	
	-- match the name of the elements
	if (select @xml1.value('(local-name((/*)[1]))','nvarchar(max)'))
	   <>
	   (select @xml2.value('(local-name((/*)[1]))','nvarchar(max)'))
    begin
		return 1
    end
    
    -- match the value of the elements
    declare @element1 nvarchar(max), @element2 nvarchar(max)
    select
		@element1 = @xml1.value('((/*)[1])','nvarchar(max)'),
		@element2 = @xml2.value('data((/*)[1])','nvarchar(max)')
    if @element1 <> @element2
    begin
		return 1
    end
    
    -- match the number of attributes
    declare @attributes1 int, @attributes2 int
    select
		@attributes1 = @xml1.query('count(/*/@*)').value('.','int'),
		@attributes2 = @xml2.query('count(/*/@*)').value('.','int')
    
    if @attributes1 <> @attributes2
    begin
		return 1
    end
    
    -- match the attributes of attributes
    -- here we need to run a loop over each attribute in the first Xml 
    -- element and see if the same attribute exists in the second element. 
    -- if the attribute exists, we need to check if the value is the same.
    declare @count int
    declare @attribute nvarchar(max)
    declare @value nvarchar(max)
    
    select @count = 1
    
    while @count <= @attributes1
    begin
		select @attribute = null, @value = null
		select
			@attribute = @xml1.value('local-name((/*/@*[sql:variable("@count")])[1])', 'nvarchar(max)'),
			@value = @xml1.value('(/*/@*[sql:variable("@count")])[1]','nvarchar(max)')
	
		if @xml2.exist('(/*/@*[local-name()=sql:variable("@attribute")])[1]') = 0
		begin
			return 1
		end		
		
		if @xml2.value('(/*/@*[local-name()=sql:variable("@attribute")])[1]', 'nvarchar(max)') <> @value
		begin
			return 1
		end
		
		select @count = @count + 1
    end
    
    -- match the number of child elements
    declare @elements1 int, @elements2 int
    select
		@elements1 = @xml1.query('count(/*/*)').value('.','int'),
		@elements2 = @xml2.query('count(/*/*)').value('.','int')
    
    if @elements1 <> @elements2
    begin
		return 1
    end
    
    
    -- start recursive loop for each child element
    select @count = 1
    declare @child1 xml, @child2 xml
    
    while @count <= @elements1
    begin
		select
			@child1 = @xml1.query('/*/*[sql:variable("@count")]'),
			@child2 = @xml2.query('/*/*[sql:variable("@count")]')		
    
    
			if [dbo].[CompareXml](@child1, @child2) = 1
			begin
				return 1
			end
    
			select @count = @count + 1
    end
    
    return @return
end

